﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace BuildCode.Models
{
    public class OracleDataBase : IDataBase
    {
        string strConnection = "Data Source=192.168.0.88:1521/ORCL;Persist Security Info=True;User ID=LEILI_ABP_MES;Password=123456;";
        const string provider = "System.Data.OracleClient";
        string dbOwner = "";
        private DbHelper dbHelper;
        public void SetConfig(string connection,string owner)
        {
            strConnection = connection;
            dbOwner = owner;
        }
        public List<ColumnInfo> GetColumnList(string TableName)
        {
            string sql = $@"SELECT
	                        C.TABLE_NAME `TableName`,
	                        C.COLUMN_NAME `ColumnName`,
	                        C.DATA_TYPE `TypeName`,
	                        C.DATA_TYPE_OWNER,
	                        C.DATA_LENGTH ,
	                        C.DATA_PRECISION `Precision`,
	                        C.DATA_SCALE `Scale`,
	                        CASE C.NULLABLE
	                        WHEN 'Y' THEN 0 ELSE 1
                          END `NotNUll`,
	                        C.COLUMN_ID `ColumsID`,
	                        C.DATA_DEFAULT `DefaultValue`,
	                        C.CHAR_LENGTH `MaxLength`,
	                        COM.COMMENTS `Comment`,
	                        1 AS `IsCheck`,
                            1 AS `IsDataColumn`
                        FROM
	                        SYS.ALL_TAB_COLUMNS C,
	                        SYS.ALL_COL_COMMENTS COM 
                        WHERE
	                        COM.OWNER ( + ) = C.OWNER 
	                        AND COM.TABLE_NAME ( + ) = C.TABLE_NAME 
	                        AND COM.COLUMN_NAME ( + ) = C.COLUMN_NAME 
	                        AND C.OWNER = '{dbOwner}' 
	                        AND C.TABLE_NAME = '{TableName}' 
                        ORDER BY C.COLUMN_ID ASC";
            sql = sql.Replace('`', '"');
            dbHelper = new DbHelper(strConnection, provider);
            DataTable dt = dbHelper.GetTable(sql);
            var list = dt.ToList<ColumnInfo>();
            return list;
        }

        public List<TableInfo> GetTableList()
        {
            string sql = $@"SELECT T.TABLE_NAME `TableName`, T.COMMENTS `Comment`, T.OWNER FROM ALL_TAB_COMMENTS T WHERE TABLE_TYPE = 'TABLE' AND OWNER = '{dbOwner}'";
            sql = sql.Replace('`', '"');
            dbHelper = new DbHelper(strConnection, provider);
            DataTable dt = dbHelper.GetTable(sql);
            var list = dt.ToList<TableInfo>();
            return list;
        }

        public void RefreshData()
        {
            throw new NotImplementedException();
        }

        public string CheckConnection()
        {
            dbHelper = new DbHelper(strConnection, provider);
            return dbHelper.GetStatus();
        }
    }
}